package com.tfjybj.itoo.exam.provider.until;

import com.tfjybj.itoo.exam.model.ExamExamRoomListModel;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


@Slf4j
public class MutliStyleExcelUtil {

    /**
     * 多行表头
     * dataList：导出的数据；sheetName：表头名称； head0：表头第一行列名；headnum0：第一行合并单元格的参数
     * head1：表头第二行列名；headnum1：第二行合并单元格的参数；detail：导出的表体字段 List<Map<String, Object>> dataList,
     */
    public static void reportMergeXls(HttpServletRequest request,
                                      HttpServletResponse response,
                                      List<ExamExamRoomListModel> newInfomation, String[] head0, String[] headnum0,
                                      String[] head1, String[] detail)
            throws Exception {

        HSSFWorkbook workbook = new HSSFWorkbook();
//        for (int q = 0; q <sheetName.length ; q++) {
        int k = 0;
        for (ExamExamRoomListModel examExamRoomListModel : newInfomation) {
            // 构造导出数据
            List<Map<String, Object>> dataListLeft = new ArrayList<Map<String, Object>>();
            List<Map<String, Object>> dataListRight = new ArrayList<Map<String, Object>>();
            int stuSum = examExamRoomListModel.getChildList().size();
            // 行数计数器
            // 定义一个索引,用来计数学生个数
            for (int rowIndex = 0; rowIndex < examExamRoomListModel.getChildList().size(); rowIndex++) {
                Map<String, Object> tmpMap = new HashMap<String, Object>();
                int j = rowIndex % 100;
                if (j < 50) {
                    // 如果超过第一张的容量则开始第二页
                    if (j == 0 && (dataListRight.size() + dataListLeft.size()) % 100 == 0 && (dataListRight.size() + dataListLeft.size()) > 0) {
                        k++;
                        examExamRoomListModel.setExamRoomName(examExamRoomListModel.getExamRoomName() + "_" + k);
                        createSheet(head0, headnum0, head1, detail, workbook, examExamRoomListModel, dataListLeft, dataListRight, stuSum);
                        // 清空map,第二页从新考试存储
                        dataListLeft = new ArrayList<Map<String, Object>>();
                        dataListRight = new ArrayList<Map<String, Object>>();
                    }
                    // 左半部分表格赋值
                    tmpMap.put("NameOne", examExamRoomListModel.getChildList().get(rowIndex).getExamineeName());
                    tmpMap.put("stuCodeOne", examExamRoomListModel.getChildList().get(rowIndex).getExamineeCode());
                    tmpMap.put("classOne", examExamRoomListModel.getChildList().get(rowIndex).getExamineeClass());
                    tmpMap.put("seatNumberOne", "");
                    tmpMap.put("studentSignatureOne", "");
                    dataListLeft.add(tmpMap);
                } else{
                    // 右半部分赋值
                    tmpMap.put("NameTwo", examExamRoomListModel.getChildList().get(rowIndex).getExamineeName());
                    tmpMap.put("stuCodeTwo", examExamRoomListModel.getChildList().get(rowIndex).getExamineeCode());
                    tmpMap.put("classTwo", examExamRoomListModel.getChildList().get(rowIndex).getExamineeClass());
                    // 座位号
                    tmpMap.put("seatNumberTwo", "");
                    // 学生签字
                    tmpMap.put("studentSignatureTwo", "");
                    dataListRight.add(tmpMap);
                }

            }
            // 第一页没有占满（小于100条）
            if (dataListLeft.size() > 0 || dataListRight.size() > 0) {
                k++;
                examExamRoomListModel.setExamRoomName(examExamRoomListModel.getExamRoomName() + "-" + k);
                createSheet(head0, headnum0, head1, detail, workbook, examExamRoomListModel, dataListLeft, dataListRight, stuSum);
            }
        }

        String namenee = "考生签到表";
        String fileName = new String(namenee.getBytes("utf-8"),"ISO-8859-1");
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        response.setContentType("application/x-download;charset=utf-8");
        response.addHeader("Content-Disposition", "attachment;filename="
                + fileName + ".xls");
        OutputStream os = response.getOutputStream();
        ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray());
        byte[] b = new byte[1024];
        while ((bais.read(b)) > 0) {
            os.write(b);
        }
        bais.close();
        os.flush();
        os.close();
    }

    private static void createSheet(String[] head0, String[] headnum0, String[] head1, String[] detail, HSSFWorkbook workbook,
                                    ExamExamRoomListModel examExamRoomListModel, List<Map<String, Object>> dataListLeft, List<Map<String, Object>> dataListRight, Integer stuSum) {

        HSSFSheet sheet = workbook.createSheet(examExamRoomListModel.getExamRoomName());// 创建一个表
        // 表头标题样式
        HSSFFont headfont = workbook.createFont();
        headfont.setFontName("宋体");
        headfont.setFontHeightInPoints((short) 22);// 字体大小
        HSSFCellStyle headstyle = workbook.createCellStyle();
        headstyle.setFont(headfont);
        headstyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        headstyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        headstyle.setLocked(true);
        // 表头时间样式
        HSSFFont datefont = workbook.createFont();
        datefont.setFontName("宋体");
        datefont.setFontHeightInPoints((short) 9);// 字体大小
        HSSFCellStyle datestyle = workbook.createCellStyle();
        datestyle.setFont(datefont);
        datestyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        datestyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        datestyle.setLocked(true);
        // 列名样式
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 9);// 字体大小
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        style.setLocked(true);
        // 普通单元格样式（中文）
        HSSFFont font2 = workbook.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 9);
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setBorderBottom(BorderStyle.THIN); //下边框
        style2.setBorderLeft(BorderStyle.THIN);//左边框
        style2.setBorderTop(BorderStyle.THIN);//上边框
        style2.setBorderRight(BorderStyle.THIN);//右边框
        style2.setFont(font2);
        style2.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        style2.setWrapText(true); // 换行
        style2.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        // 设置列宽  （第几列，宽度）
        sheet.setColumnWidth(0, 1950);
        sheet.setColumnWidth(1, 2300);
        sheet.setColumnWidth(2, 2800);
        sheet.setColumnWidth(3, 4000);
        sheet.setColumnWidth(4, 1300);
        sheet.setColumnWidth(5, 1800);
        sheet.setColumnWidth(6, 2300);
        sheet.setColumnWidth(7, 2800);
        sheet.setColumnWidth(8, 4000);
        sheet.setColumnWidth(9, 1300);
        sheet.setColumnWidth(10, 1800);
        sheet.setDefaultRowHeight((short) 360);//设置行高
        // 第一行表头标题
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, head0.length));
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 0x349);
        HSSFCell cell = row.createCell(1);
        cell.setCellStyle(headstyle);
        CellUtil.setCellValue(cell, "廊坊师范学院考场情况登记表");
        // 第二行时间
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, head0.length));
        HSSFRow row1 = sheet.createRow(1);
        row.setHeight((short) 0x349);
        HSSFCell cell1 = row1.createCell(1);
        cell1.setCellStyle(datestyle);
        CellUtil.setCellValue(cell1, examExamRoomListModel.getExamName());
        // 第三行表头列名
        row = sheet.createRow(2);
        String variable = "";
        for (int i = 0; i < 10; i++) {
            /**
             * 0-4 :考试时间
             * 5-7  :地点
             * 8-9: 考场人数
             */
            if (i == 0 || i == 1 || i == 2 || i == 3 || i == 4) {
                variable = ":" + dataToWeek(examExamRoomListModel.getExamDate().trim()) + "(" + examExamRoomListModel.getExamDate() + ")" + "(" + examExamRoomListModel.getExamTimeSlot() + ")";
            } else if (i == 5 || i == 6 || i == 7) {
                variable = ":" + examExamRoomListModel.getExamRoomName();
            } else if (i == 8 || i == 9) {
                variable = ":" + stuSum.toString();
            }

            cell = row.createCell(i + 1);
            cell.setCellValue(head0[i] + variable);
            cell.setCellStyle(style);
        }
        //动态合并单元格
        for (int i = 0; i < headnum0.length; i++) {
            String[] temp = headnum0[i].split(",");
            Integer startrow = Integer.parseInt(temp[0]);
            Integer overrow = Integer.parseInt(temp[1]);
            Integer startcol = Integer.parseInt(temp[2]);
            Integer overcol = Integer.parseInt(temp[3]);
            sheet.addMergedRegion(new CellRangeAddress(startrow, overrow,
                    startcol, overcol));
        }
        //设置合并单元格的参数并初始化带边框的表头（这样做可以避免因为合并单元格后有的单元格的边框显示不出来）
        //因为下标从0开始，所以这里表示的是excel中的第四行
        row = sheet.createRow(3);
        //设置excel中第四行的列边框
        cell.setCellStyle(style);
        for (int j = 0; j < head1.length; j++) {
            cell = row.createCell(j+1);
            //给excel中第四行的列赋值
            cell.setCellValue(head1[j]);
            //设置excel中第四行的列的边框
            cell.setCellStyle(style);
        }
        // 设置列值-内容
        for (int i = 0; i < dataListLeft.size(); i++) {
            //标题、时间、表头字段共占了4行，所以在填充数据的时候要加4，也就是数据要从第5行开始填充
            row = sheet.createRow(i + 4);
            for (int j = 0; j < detail.length / 2; j++) {
                Map tempmap = dataListLeft.get(i);
                Object data = tempmap.get(detail[j]);
                cell = row.createCell(j + 1);
                cell.setCellStyle(style2);
                cell.setCellType(CellType.STRING);
                CellUtil.setCellValue(cell, data);
            }
        }

        for (int i = 0; i < dataListRight.size(); i++) {
            //(get到上面createRow的行,直接填充)标题、时间、表头字段共占了4行，所以在填充数据的时候要加4，也就是数据要从第5行开始填充
            row = sheet.getRow(i + 4);
            for (int j = detail.length / 2; j < detail.length; j++) {
                Map tempmap = dataListRight.get(i);
                Object data = tempmap.get(detail[j]);
                cell = row.createCell(j + 1);
                cell.setCellStyle(style2);
                cell.setCellType(CellType.STRING);
                CellUtil.setCellValue(cell, data);
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(54, 54, 0, head0.length + 1));
        /**
         * 表未主
         */
        HSSFCellStyle tablePattern = workbook.createCellStyle();
        tablePattern.setBorderLeft(BorderStyle.THIN);// 左右居中
        tablePattern.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        tablePattern.setLocked(true);
        sheet.addMergedRegion(new CellRangeAddress(55, 55, 1, head0.length));
        HSSFRow explain = sheet.createRow(55);
        HSSFCell explainCell1 = explain.createCell(1);
        explainCell1.setCellStyle(tablePattern);
        CellUtil.setCellValue(explainCell1, "考场情况说明:");

        sheet.addMergedRegion(new CellRangeAddress(56, 56, 1, head0.length));
        HSSFRow jishu = sheet.createRow(56);
        HSSFCell persion = jishu.createCell(1);
        persion.setCellStyle(tablePattern);
        CellUtil.setCellValue(persion, "技术人员:");

        /**
         * 监考老师
         */
        sheet.addMergedRegion(new CellRangeAddress(57, 57, 1, 2));
        HSSFRow ccc = sheet.createRow(57);
        HSSFCell teaOne = ccc.createCell(1);
        teaOne.setCellStyle(tablePattern);
        CellUtil.setCellValue(teaOne, "主监考老师:");

        HSSFCell teaTwo = ccc.createCell(4);
        teaTwo.setCellStyle(tablePattern);
        CellUtil.setCellValue(teaTwo, "副监考老师:");
    }



    /**
     * 给定日期转换相应的星期
     *
     * @param datetime
     * @return
     * @author 马莹
     * @since 2019-1-8 09:46:00
     */

    private static String dataToWeek(String datetime) {
        SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
        String[] weekDays = {"星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六"};
        Calendar cal = Calendar.getInstance(); // 获得一个日历
        try {
            cal.setTime(f.parse(datetime));
        } catch (ParseException e) {
            e.printStackTrace();
            log.warn(e + "");
        }
        int w = cal.get(Calendar.DAY_OF_WEEK) - 1; // 指示一个星期中的某天。
        if (w < 0)
            w = 0;
        return weekDays[w];
    }
}
